<?
/***************************************************************************
 *   copyright : (C) 2009 Udrea Cristian
 *   site : http://code.google.com/p/testauction-php
 ***************************************************************************/

/***************************************************************************
 *   This program is free software; you can redistribute it and/or modify
 *   it under the terms of the GNU Lesser General Public License as 
 *   published by the Free Software Foundation; either version 2 of the
 *   License, or (at your option) any later version. Although none of the
 *   code may be sold. If you have been sold this script, get a refund.
 ***************************************************************************/
include("constants.php");
include("sql_check.php");
include("db_func.php");

class MySQLDB
{
	var $num_active_users;
	var $num_active_guests;
	var $num_members;

	public function __construct() {
		$this->num_members = -1;
		if(TRACK_VISITORS) {
			/* Calculate number of users at site */
			$this->calcNumActiveUsers();
			/* Calculate number of guests at site */
			$this->calcNumActiveGuests();
		}
    }

	public function confirmUserPass($username, $password) {
		$username = stripslashes(clean_variable($username));
		$password = stripslashes(clean_variable($password));
		$chk_array= array($username, $password);
		sql_check($chk_array);
		/* Search for unique user key (needed to fetch password) */
		$resultset = execute_query('SELECT userkey FROM '.TBL_USERS.' WHERE username = ?', array($username));
		$userkey = (count($resultset) == 1) ? $resultset[0]['userkey'] : '';
		if($userkey == ''){
			//echo "Could not complete operation! TO_REMOVE : USERKEY NOT FOUND<br />";
			return 3;
		}
		/* Finally fetch password */
		$resultset = execute_query('SELECT password FROM '.TBL_PASSWORDS.' WHERE userkey = ?', array($userkey));
		$chkpassword = (count($resultset) == 1) ? $resultset[0]['password'] : '';
		/* Validate that password is correct */
		if($password == $chkpassword){
			return 0; //Success! Username and password confirmed
		}
		else{
			return 2; //Indicates password failure
		}
	}
	
	public function confirmUserID($username, $userid) {
		/* Check against injection */
		$username = stripslashes(clean_variable($username));
		$chk_array= array($username);
		sql_check($chk_array);
		/* Verify that user is in database */
		$resultset = execute_query('SELECT userid FROM '.TBL_USERS.' WHERE username = ?', array($username));
		if(count($resultset) == 0)
			return 1; //Indicates username failure
		/* Retrieve userid from result, strip slashes */
		$resultset[0]['userid'] = stripslashes($resultset[0]['userid']);
		$userid = stripslashes($userid);
		/* Validate that userid is correct */
		if($userid == $resultset[0]['userid'])
			return 0; //Success! Username and userid confirmed
		else
			return 2; //Indicates userid invalid
	}

	/**
    * usernameTaken - Returns true if the username has
    * been taken by another user, false otherwise.
    */
	public function usernameTaken($username) {
		/* Check against injection */
		$username = stripslashes(clean_variable($username));
		$chk_array= array($username);
		sql_check($chk_array);
		$resultset = execute_query('SELECT username FROM '.TBL_USERS.' WHERE username = ?', array($username));
		return (count($resultset) > 0);
	}

    /**
    * usernameBanned - Returns true if the username has
    * been banned by the administrator.
    */
    public function usernameBanned($username)
    {
		/* Check against injection */
		$username = stripslashes(clean_variable($username));
		$chk_array= array($username);
		sql_check($chk_array);
		$resultset = execute_query('SELECT username FROM '.TBL_BANNED_USERS.' WHERE username = ?', array($username));
		return (count($resultset) > 0);
    }

    /**
    * addNewUser - Inserts the given (username, password, email)
    * info into the database. Appropriate user level is set.
    * Returns true on success, false otherwise.
    */
	public function addNewUser($username, $password, $email, $surname, $name, $ulevel){
		/* Check against injection */
		$chk_array = array($username, $password);
		sql_check($chk_array);
		$time = time();
		$userkey = generateUserKey();
		if(execute_update('INSERT INTO '.TBL_PASSWORDS.' VALUES (?, ?)', array($userkey, $password)))
			if(execute_update('INSERT INTO '.TBL_USERDATA.' VALUES (?, ?, ?, \'\', \'\', \'\', \'\', \'\')', array($userkey, $surname, $name)))
				if(execute_update('INSERT INTO '.TBL_USERS.' VALUES (?, 0, ?, ?, ?, 0, 0, 0, 0, 0, ?)', 
					array($username, $ulevel, $userkey, $email, $time)))
					if(execute_update('INSERT INTO '.TBL_CREDIT.' VALUES (?, 0, 1, ?)', array($userkey, BONUS_OFFER_COUNT))){
						return TRUE;
					}
		return FALSE;
	}

	/**
	* updateUserField - Updates a field, specified by the field
    * parameter, in the user's row of the database.
    */
    public function updateUserField($username, $field, $value) {
		return execute_update('UPDATE '.TBL_USERS.' SET '.$field.' = ? WHERE username = ?', array($value, $username));
	}
	
	public function updateUserDataField($userkey, $field, $value) {
		return execute_update('UPDATE '.TBL_USERDATA.' SET '.$field.' = ? WHERE userkey = ?', array($value, $userkey));
	}
	
	/**
    * getUserInfo - Returns the result array from a mysql
    * query asking for all information stored regarding
    * the given username. If query fails, NULL is returned.
    */
    public function getUserInfo($username) {
		$resultset = execute_query('SELECT * FROM '.TBL_USERS.' WHERE username = ?', array($username));
		return $resultset[0];
	}
	
	/**
    * getNumMembers - Returns the number of signed-up users
    * of the website, banned members not included. The first
    * time the function is called on page load, the database
    * is queried, on subsequent calls, the stored result
    * is returned. This is to improve efficiency, effectively
    * not querying the database when no call is made.
    */
    public function getNumMembers() {
		if($this->num_members < 0) {
			$result = execute_query('SELECT * FROM '.TBL_USERS);
			$this->num_members = count($result);
		}
		return $this->num_members;
   }
   
    /**
    * calcNumActiveUsers - Finds out how many active users
    * are viewing site and sets class variable accordingly.
    */
    public function calcNumActiveUsers() {
		/* Calculate number of users at site */
		$result = execute_query('SELECT * FROM '.TBL_ACTIVE_USERS);
		$this->num_active_users = count($result);
	}
	
	/**
    * calcNumActiveGuests - Finds out how many active guests
    * are viewing site and sets class variable accordingly.
    */
    public function calcNumActiveGuests() {
		/* Calculate number of guests at site */
		$result = execute_query('SELECT * FROM '.TBL_ACTIVE_GUESTS);
		$this->num_active_guests = count($result);
	}
	
	/**
    * addActiveUser - Updates username's last active timestamp
    * in the database, and also adds him to the table of
    * active users, or updates timestamp if already there.
    */
	public function addActiveUser($username, $time) {
		execute_update('UPDATE '.TBL_USERS.' SET timestamp = ? WHERE username = ?', array($time, $username));
		if(!TRACK_VISITORS) return;
		execute_update('REPLACE INTO '.TBL_ACTIVE_USERS.' VALUES (?, ?)', array($username, $time));
		$this->calcNumActiveUsers();
	}
	
	/* addActiveGuest - Adds guest to active guests table */
	public function addActiveGuest($ip, $time) {
		if(!TRACK_VISITORS) return;
		execute_update('REPLACE INTO '.TBL_ACTIVE_GUESTS.' VALUES (?, ?)', array($ip, $time));
		$this->calcNumActiveGuests();
	}
	
	/* These functions are self explanatory, no need for comments */
   
    /* removeActiveUser */
    public function removeActiveUser($username) {
		if(!TRACK_VISITORS) return;
		execute_update('DELETE FROM '.TBL_ACTIVE_USERS.' WHERE username = ?', array($username));
		$this->calcNumActiveUsers();
	}
   
	/* removeActiveGuest */
	public function removeActiveGuest($ip) {
		if(!TRACK_VISITORS) return;
		execute_update('DELETE FROM '.TBL_ACTIVE_GUESTS.' WHERE ip = ?', array($ip));
		$this->calcNumActiveGuests();
	}
   
	/* removeInactiveUsers */
	public function removeInactiveUsers() {
		if(!TRACK_VISITORS) return;
		$timeout = time()-USER_TIMEOUT*60;
		execute_update('DELETE FROM '.TBL_ACTIVE_USERS.' WHERE timestamp < ?', array($timeout));
		$this->calcNumActiveUsers();
	}

	/* removeInactiveGuests */
	public function removeInactiveGuests() {
		if(!TRACK_VISITORS) return;
		$timeout = time()-GUEST_TIMEOUT*60;
		execute_update('DELETE FROM '.TBL_ACTIVE_GUESTS.' WHERE timestamp < ?', array($timeout));
		$this->calcNumActiveGuests();
	}
	
	/* addTempUser */
	public function addTempUser($user, $email, $time) {
		/*Generate code and e-mail hash*/
		$confirm_code = generateUniqueActivationCode();
		$confirm_email = md5($email);
		/*Add to temporary users table*/
		execute_update('INSERT INTO '.TBL_TEMP_USERS.' VALUES (?, ?, ?, ?)', array($user, $confirm_code, $confirm_email, $time));
		$array = array($confirm_code, $confirm_email);
		return $array;
	}
	
	public function isUserActive($user) {
		$result = execute_query('SELECT * FROM '.TBL_ACTIVE_USERS.' WHERE username = ?', array($user));
		return (count($result) != 0) ? TRUE : FALSE;
	}
	
	public function isUserConfirmed($user){
		$result = execute_query('SELECT confirmed FROM '.TBL_USERS.' WHERE username = ?', array($user));
		return ($result[0]['confirmed'] == 1) ? TRUE : FALSE;
	}
}
?>
